package net.aaa.hotel.dao.impl;

import net.aaa.hotel.dao.ChukuDao;
import net.aaa.hotel.entity.PageInfo;
import net.aaa.hotel.util.BaseDB;
import net.aaa.hotel.util.LayDate;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class ChukuDaoImpl implements ChukuDao {
    @Override
    public List<Map> query(PageInfo pagl) {
        String sql = "SELECT x.id,x.num,y.`name` AS yname,y.id AS yid,z.`name` AS zname,z.id AS zid,x.date,x.`status`  from t_chuku x,h_yg y,h_yg z WHERE x.cukuid=y.id AND x.jsr=z.id order by x.id desc";
        if (pagl.getCondition() != null && pagl.getCondition() != "") {
            sql += pagl.getCondition();
        }
        sql += " limit ?,?";
        int stat = (pagl.getCurr() - 1) * pagl.getNums();
        return BaseDB.queryMap(sql, stat, pagl.getNums());
    }

    @Override
    public int count(PageInfo pagl) {
        String sql = "SELECT COUNT(*) a from t_chuku x,h_yg y,h_yg z WHERE x.cukuid=y.id AND x.jsr=z.id";
        if (pagl.getCondition() != null && pagl.getCondition() != "") {
            sql += pagl.getCondition();
        }
        int a = Integer.parseInt(BaseDB.queryMap(sql).get(0).get("a") + "");

        return a;
    }

    @Override
    public List<Map> queryCuku(Integer id) {
        String sql = "SELECT cd.id id,se.ssName sname,tp.`name` pname,cu.id rid,se.ssGys gys,cd.count count,se.ssId sid from t_chukudetail cd,t_chuku cu,t_sheshi se,t_ptype tp WHERE cd.ckid=cu.id AND cd.ssid=se.ssId AND se.ssTypeid=tp.id AND cu.id=?;";

        return BaseDB.queryMap(sql, id);
    }

    @Override
    public List<Map> queryB() {
        String sql = "select * from h_yg where jueseid=5";
        return BaseDB.queryMap(sql);
    }

    @Override
    public int addCu(Map map) {
        /* map{"totalcount":"5","rukuid":"","jsr":"10"}*/
        String sql = "insert into t_chuku (cukuid,num,date,jsr)values(?,?,now(),?)";
        int i = BaseDB.insertGetKey(sql, map.get("rukuid"), map.get("totalcount"), map.get("jsr"));
        return i;
    }

    @Override
    public int addCud(Map map1, int a) {
        String sql1 = "select ssid from t_sheshi WHERE ssTypeid=1";
        List<Map> maps = BaseDB.queryMap(sql1);
        String sql = "insert into t_chukudetail (ckid,ssid,count)values(?,?,?)";
        int b = 0;
        for (Map map : maps) {
            b += BaseDB.update(sql, a, map.get("ssid"), map1.get("totalcount"));
        }
        return b;
    }

    @Override
    public int addS(Map map1) {
        String sql = "update t_sheshi set ssCount=ssCount-? where ssId=?";
        return BaseDB.update(sql,map1.get("count"), map1.get("ssid"));
    }

    @Override
    public Map<String, List> getRuMaMoney(LayDate layDate) {
        // 定义默认起始月
        int minMonth = 1;
        // 定义默认结束月
        int maxMonth = 12;
        // 拼接查询入库金额的sql
        String sql = "SELECT MONTH(date) month,SUM(zprice) money FROM t_ruku tr WHERE 1=1";
        if (layDate.getYear() != null && layDate.getYear() != "") {
            sql += " and year(date) = " + layDate.getYear();
        } else {
            sql += " and year(date) = 2020";
        }
        if (layDate.getMonthMin() != null && layDate.getMonthMin() != "") {
            sql += " and month(date) >=" + layDate.getMonthMin();
            minMonth = Integer.parseInt(layDate.getMonthMin());
        } else {
            sql += " and month(date) >= 1";
        }
        if (layDate.getMonthMax() != null && layDate.getMonthMax() != "") {
            sql += " and month(date) <=" + layDate.getMonthMax();
            maxMonth = Integer.parseInt(layDate.getMonthMax());
        } else {
            sql += " and month(date) <= 12";
        }
        sql += " group by month(date)";

        // 拼接查询出库金额的SQL
        String sql2 = "SELECT MONTH(indate) month,SUM(rzmoney) money FROM t_ruzhu  WHERE 1=1 ";
        if (layDate.getYear() != null && layDate.getYear() != "") {
            sql2 += " and year(indate) = " + layDate.getYear();
        } else {
            sql2 += " and year(indate) = 2020";
        }
        if (layDate.getMonthMin() != null && layDate.getMonthMin() != "") {
            sql2 += " and month(indate) >=" + layDate.getMonthMin();
        } else {
            sql2 += " and month(indate) >= 1";
        }
        if (layDate.getMonthMax() != null && layDate.getMonthMax() != "") {
            sql2 += " and month(indate) <=" + layDate.getMonthMax();
        } else {
            sql2 += " and month(indate) <= 12";
        }
        sql2 += " group by month(indate)";


        // 查询入库金额的结果
        List<Map> list = BaseDB.queryMap(sql);

        // 查询经营金额的结果
        List<Map> list2 = BaseDB.queryMap(sql2);

        // 定义一个Map 装载 处理后的数据，页面要的数据类型
        Map<String, List> mapList = new HashMap<>(64);

        // 装填月份的list
        List<Object> monthData = new ArrayList<>();

        // 装填入库金额的list
        List<Double> ruKuMoney = new ArrayList<>();

        // 装填经营金额的list
        List<Double> manageMoney = new ArrayList<>();

        //月份的循环
        for (int i = minMonth; i <= maxMonth; i++) {
            double value = 0;
            double value2 = 0;
            monthData.add(i + "");
            for (Map map : list) {
                // 如果 查到的map中有相同的月份，则将对应的钱数存到ruKuMoney对应的月份的位置
                if (Integer.parseInt(map.get("month") + "") == i) {
                    value = Double.parseDouble(map.get("money") + "");
                }
            }
            for (Map map : list2) {
                // 如果 查到的map中有相同的月份，则将对应的钱数存到ckMoney对应的月份的位置
                if (Integer.parseInt(map.get("month") + "") == i) {
                    value2 = Double.parseDouble(map.get("money") + "");
                }
            }
            // 如果 查到的map中没有相同的月份，0 对应的月份的位置
            ruKuMoney.add(value);
            manageMoney.add(value2);
        }
        mapList.put("ruKuMoney", ruKuMoney);
        mapList.put("manageMoney", manageMoney);
        mapList.put("monthData", monthData);

        return mapList;
    }

    @Override
    public List<Map> getAllTypeAndMoney() {
        String sql = "SELECT tp.name name,SUM(rz.rzmoney) value FROM t_ruzhu rz,t_order td,t_type tp WHERE rz.orderid=td.id AND td.roomtype=tp.id GROUP BY tp.name";
        return BaseDB.queryMap(sql);
    }

    @Override
    public int queryDingdDan() {
        String sql = "SELECT * FROM t_chuku WHERE STATUS=0";
        if (BaseDB.queryMap(sql).size() > 0) {
            return 1;
        }
        return 0;
    }

    @Override
    public List<Map> queryRuKuDatil(Integer rkid) {
        String sql = "SELECT * FROM t_chukudetail WHERE ckid=?";
        return BaseDB.queryMap(sql, rkid);
    }

    @Override
    public int upda(Integer rkid) {
        String sql = "update t_chuku set status=1 where id=?";
        return BaseDB.update(sql, rkid);
    }

    @Override
    public List<Map> queryTao() {
        String sql = "SELECT tp.`name`,ts.ssName,ts.ssGys FROM t_sheshi ts,t_ptype tp WHERE ts.ssTypeid=tp.id AND tp.id=1";

        return BaseDB.queryMap(sql);
    }

    @Override
    public int queryTa() {
        String sql = "select MIN(ssCount) a from t_sheshi WHERE ssTypeid=1";
        return Integer.parseInt(BaseDB.queryMap(sql).get(0).get("a") + "");
    }

    @Override
    public int yu(Integer rkid) {
        String sql = "select jsr,num from t_chuku where id=?";
        List<Map> maps = BaseDB.queryMap(sql, rkid);
        String sql1 = "select * from yu where id=?";
        List<Map> maps1 = BaseDB.queryMap(sql1, maps.get(0).get("jsr"));
        int q = 0;
        int e = 0;
        if (maps1.size() > 0) {
            String sql2 = "update yu set count=count+? where id=?";
            q = BaseDB.update(sql2, maps.get(0).get("num"), maps1.get(0).get("id"));
        } else {
            String sql3 = "insert into yu (id,count)values(?,?)";
            e = BaseDB.update(sql3, maps.get(0).get("jsr"), maps.get(0).get("num"));
        }

        if (q > 0 || e > 0) {
            return 1;
        }
        return 0;
    }

    @Override
    public List<Map> queryYu() {
        String sql = "SELECT y.id,yg.name,yg.imgpath,y.count from yu y,h_yg yg WHERE y.id=yg.id";
        return BaseDB.queryMap(sql);
    }

    @Override
    public List<Map> fangSu() {
        List<Map> maps = new ArrayList<>();
        HashMap<Object, Integer> ma = new HashMap<>();
        //在住
        String sql = "SELECT COUNT(*) a FROM t_room WHERE status =2";
        //空房
        String sql1 = "SELECT COUNT(*) a FROM t_room WHERE status <=1";
        //预抵数
        String sql2 = "SELECT SUM(count) a  FROM t_order WHERE DAY(rzdate)=DAY(NOW()) AND status=0";
        //预离数
        String sql3 = "SELECT SUM(count) a  FROM t_order WHERE DAY(leavedate)=DAY(NOW()) AND status=1";
        int a =0;
        int b =0;
        int c =0;
        int d =0;
        Object a1 = BaseDB.queryMap(sql).get(0).get("a");
        if (a1 != null) {
            a = Integer.parseInt(a1 + "");
        }



        Object a2 = BaseDB.queryMap(sql1).get(0).get("a");
        if (a2!=null){
            b=Integer.parseInt(a2 + "");
        }




        Object a3 = BaseDB.queryMap(sql2).get(0).get("a");
        if (a3!=null){

            c = Integer.parseInt(a3+"");
        }




        Object a4 = BaseDB.queryMap(sql3).get(0).get("a");
        if (a4!=null){
             d = Integer.parseInt(a4+"");
        }


        ma.put("zzhu",a);
        ma.put("kfang",b);
        ma.put("dcount",c);
        ma.put("lcount",d);
        maps.add(ma);
        return maps;
    }
}
